﻿IF OBJECT_ID ('CHO_TASK_GRPS') IS NOT NULL
	DROP TABLE CHO_TASK_GRPS
GO

CREATE TABLE CHO_TASK_GRPS
	(
	TASK_GRP_ID   INT NOT NULL,
	TASK_GRP_NAME NVARCHAR (50) NOT NULL UNIQUE,
	TASK_GRP_DESC NVARCHAR (2500),
	DELETED	      BIT NOT NULL,
	CREATED_BY	  NVARCHAR(100) NOT NULL,
	CREATED_DATE  DATETIME NOT NULL,
	MODIFIED_BY	  NVARCHAR(100) NOT NULL,
	MODIFIED_DATE DATETIME NOT NULL,
	PARENT		  INT,
	CONSTRAINT PK_CHO_TASK_GRPS PRIMARY KEY (TASK_GRP_ID)
	)
GO

CREATE INDEX IX_CHO_TASK_GRPS
	ON CHO_TASK_GRPS (TASK_GRP_NAME)
GO
----------------------------------------------------------------

IF OBJECT_ID ('CHO_TASK_GRPS_AUDIT') IS NOT NULL
	DROP TABLE CHO_TASK_GRPS_AUDIT
GO

CREATE TABLE CHO_TASK_GRPS_AUDIT
	(
	TASK_GRP_ID   INT NOT NULL,
	TASK_GRP_NAME NVARCHAR (50) NOT NULL,
	TASK_GRP_DESC NVARCHAR (2500),
	DELETED       BIT NOT NULL,
	CREATED_BY	  NVARCHAR(100) NOT NULL,
	CREATED_DATE  DATETIME NOT NULL,
	MODIFIED_BY   NVARCHAR (100) NOT NULL,
	MODIFIED_DATE DATETIME NOT NULL,
	PARENT		  INT,
	CONSTRAINT PK_CHO_TASK_GRPS_AUDIT PRIMARY KEY (TASK_GRP_ID, MODIFIED_DATE)
	)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_SELECT_TASK_GRPS') IS NOT NULL
	DROP PROC CHO_SELECT_TASK_GRPS
GO

CREATE PROCEDURE CHO_SELECT_TASK_GRPS
		AS

	SET NOCOUNT ON

	SELECT TASK_GRP_ID, TASK_GRP_NAME, TASK_GRP_DESC, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, PARENT FROM CHO_TASK_GRPS WHERE DELETED = 0
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_ADD_OR_UPDATE_TASK_GRPS') IS NOT NULL
	DROP PROC CHO_ADD_OR_UPDATE_TASK_GRPS
GO

CREATE PROCEDURE CHO_ADD_OR_UPDATE_TASK_GRPS
		@TASK_GRP_NAME NVARCHAR(50),
		@TASK_GRP_DESC NVARCHAR(2500),
		@USER_NAME NVARCHAR(100),
		@TASK_GRP_ID INT OUTPUT
		AS
	
	SET NOCOUNT ON

	SELECT @TASK_GRP_ID = NULL

	DECLARE @DATE_UPDATED DATETIME
	DECLARE @DELETED BIT
	DECLARE @ERR_MESSAGE NVARCHAR(255)

	SELECT @TASK_GRP_ID = TASK_GRP_ID, @DELETED = DELETED FROM CHO_TASK_GRPS WHERE TASK_GRP_NAME = @TASK_GRP_NAME
	SELECT @DATE_UPDATED = GETDATE()
	
	IF @TASK_GRP_ID IS NULL
	BEGIN
		INSERT CHO_TASK_GRPS (TASK_GRP_NAME, TASK_GRP_DESC, DELETED, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
		VALUES (@TASK_GRP_NAME, @TASK_GRP_DESC, 0, @USER_NAME, @DATE_UPDATED, @USER_NAME, @DATE_UPDATED)
		SELECT @TASK_GRP_ID = @@IDENTITY
		
		INSERT INTO CHO_TASK_GRPS_AUDIT 
			SELECT * FROM CHO_TASK_GRPS
			WHERE
				TASK_GRP_NAME = @TASK_GRP_NAME		
	END
	ELSE
	BEGIN
		IF (@DELETED = 0)
		BEGIN
			UPDATE CHO_TASK_GRPS
				SET TASK_GRP_DESC = @TASK_GRP_DESC,
					MODIFIED_BY = @USER_NAME,
					MODIFIED_DATE = @DATE_UPDATED
				WHERE
					TASK_GRP_NAME = @TASK_GRP_NAME				

			INSERT INTO CHO_TASK_GRPS_AUDIT 
				SELECT * FROM CHO_TASK_GRPS
				WHERE
					TASK_GRP_NAME = @TASK_GRP_NAME
		END
		ELSE
		BEGIN
			SET @ERR_MESSAGE = @TASK_GRP_NAME + ' task group is in deleted state.'
			RAISERROR (@ERR_MESSAGE, 11,1)
			RETURN
		END
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_DELETE_TASK_GRPS') IS NOT NULL
	DROP PROC CHO_DELETE_TASK_GRPS
GO

CREATE PROCEDURE CHO_DELETE_TASK_GRPS
		@TASK_GRP_NAME NVARCHAR(50),
		@USER_NAME NVARCHAR(100),
		@TASK_GRP_ID INT OUTPUT
		AS

	SET NOCOUNT ON

	SELECT @TASK_GRP_ID = NULL

	SELECT @TASK_GRP_ID = TASK_GRP_ID FROM CHO_TASK_GRPS WHERE TASK_GRP_NAME = @TASK_GRP_NAME
	IF @TASK_GRP_ID is NULL
	BEGIN
		SELECT @TASK_GRP_ID = 0
	END
	ELSE
	BEGIN
		UPDATE CHO_TASK_GRPS SET DELETED = 1, MODIFIED_BY = @USER_NAME, MODIFIED_DATE = GETDATE() WHERE TASK_GRP_NAME = @TASK_GRP_NAME

		INSERT INTO CHO_TASK_GRPS_AUDIT 
			SELECT * FROM CHO_TASK_GRPS
			WHERE
				TASK_GRP_NAME = @TASK_GRP_NAME
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_UNDELETE_TASK_GRPS') IS NOT NULL
	DROP PROC CHO_UNDELETE_TASK_GRPS
GO

CREATE PROCEDURE CHO_UNDELETE_TASK_GRPS
		@TASK_GRP_NAME NVARCHAR(50),
		@USER_NAME NVARCHAR(100)
		AS

	SET NOCOUNT ON

	DECLARE @TASK_GRP_ID INT

	SELECT @TASK_GRP_ID = TASK_GRP_ID FROM CHO_TASK_GRPS WHERE TASK_GRP_NAME = @TASK_GRP_NAME AND DELETED = 1
	IF @TASK_GRP_ID is NULL
	BEGIN
		RETURN
	END
	ELSE
	BEGIN
		UPDATE CHO_TASK_GRPS SET DELETED = 0, MODIFIED_BY = @USER_NAME, MODIFIED_DATE = GETDATE() WHERE TASK_GRP_NAME = @TASK_GRP_NAME

		INSERT INTO CHO_TASK_GRPS_AUDIT 
			SELECT * FROM CHO_TASK_GRPS
			WHERE
				TASK_GRP_NAME = @TASK_GRP_NAME
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_IS_TASK_GRP_EXISTS') IS NOT NULL
	DROP PROC CHO_IS_TASK_GRP_EXISTS
GO


CREATE PROCEDURE CHO_IS_TASK_GRP_EXISTS
		@TASK_GRP_NAME NVARCHAR(50),
		@DELETED BIT OUTPUT,
		@TASK_GRP_ID INT OUTPUT
		AS

	SET NOCOUNT ON

	SELECT @TASK_GRP_ID = 0
	SELECT @DELETED = 0

	SELECT @TASK_GRP_ID = TASK_GRP_ID, @DELETED = DELETED FROM CHO_TASK_GRPS 
		WHERE 
			TASK_GRP_NAME = @TASK_GRP_NAME

	IF @TASK_GRP_ID is NULL
	BEGIN
		SELECT @TASK_GRP_ID = 0
	END
GO

----------------------------------------------------------------

DECLARE @TASK_GRP_ID INT
EXECUTE CHO_ADD_OR_UPDATE_TASK_GRPS 'CHUBB', 'Chubb Tasks', 'Raj', @TASK_GRP_ID OUT
SELECT "@TASK_GRP_ID" = @TASK_GRP_ID
GO

DECLARE @TASK_GRP_ID INT
EXECUTE CHO_ADD_OR_UPDATE_TASK_GRPS 'MM', 'Mass Mutual Tasks', 'Raj', @TASK_GRP_ID OUT
SELECT "@TASK_GRP_ID" = @TASK_GRP_ID
GO
